
CREATE TABLE [department]
( 
	[department_id]      char(18)  NOT NULL ,
	[department_name]    char(18)  NULL 
)
go

ALTER TABLE [department]
	ADD CONSTRAINT [XPKdepartment] PRIMARY KEY  CLUSTERED ([department_id] ASC)
go

CREATE TABLE [part_type]
( 
	[part_type_id]       char(18)  NOT NULL ,
	[part_type_name]     char(18)  NULL 
)
go

ALTER TABLE [part_type]
	ADD CONSTRAINT [XPKpart_type] PRIMARY KEY  CLUSTERED ([part_type_id] ASC)
go

CREATE TABLE [parts]
( 
	[part_id]            char(18)  NOT NULL ,
	[part_name]          char(18)  NULL ,
	[part_type_id]       char(18)  NOT NULL 
)
go

ALTER TABLE [parts]
	ADD CONSTRAINT [XPKparts] PRIMARY KEY  CLUSTERED ([part_id] ASC)
go

CREATE TABLE [pay_grade]
( 
	[pay_grade_id]       char(18)  NOT NULL ,
	[amount_per_week]    double precision  NULL 
)
go

ALTER TABLE [pay_grade]
	ADD CONSTRAINT [XPKpay_grade] PRIMARY KEY  CLUSTERED ([pay_grade_id] ASC)
go

CREATE TABLE [purchase_order]
( 
	[purord_id]          char(18)  NOT NULL ,
	[date]               char(18)  NULL ,
	[vendor_id]          char(18)  NOT NULL ,
	[purord_item_id]     char(18)  NULL 
)
go

ALTER TABLE [purchase_order]
	ADD CONSTRAINT [XPKpurchase_order] PRIMARY KEY  CLUSTERED ([purord_id] ASC)
go

CREATE TABLE [purchase_order_items]
( 
	[purord_item_id]     char(18)  NOT NULL ,
	[date]               datetime  NULL ,
	[quantity]           integer  NULL ,
	[price]              double precision  NULL ,
	[purord_id]          char(18)  NOT NULL ,
	[part_id]            char(18)  NOT NULL 
)
go

ALTER TABLE [purchase_order_items]
	ADD CONSTRAINT [XPKpurchase_order_items] PRIMARY KEY  CLUSTERED ([purord_item_id] ASC)
go

CREATE TABLE [vendor]
( 
	[vendor_id]          char(18)  NOT NULL ,
	[vendor_name]        char(18)  NULL 
)
go

ALTER TABLE [vendor]
	ADD CONSTRAINT [XPKvendor] PRIMARY KEY  CLUSTERED ([vendor_id] ASC)
go

CREATE TABLE [staff]
( 
	[id]                 char(18)  NOT NULL ,
	[name]               char(18)  NULL ,
	[department_id]      char(18)  NOT NULL ,
	[pay_grade_id]       char(18)  NOT NULL 
)
go

ALTER TABLE [staff]
	ADD CONSTRAINT [XPKstaff] PRIMARY KEY  CLUSTERED ([id] ASC)
go

CREATE TABLE [warehouse]
( 
	[warehouse_id]       char(18)  NOT NULL ,
	[geozone]            char(18)  NULL 
)
go

ALTER TABLE [warehouse]
	ADD CONSTRAINT [XPKwarehouse] PRIMARY KEY  CLUSTERED ([warehouse_id] ASC)
go

CREATE TABLE [part_inventory]
( 
	[quantity]           integer  NULL ,
	[part_type_id]       char(18)  NOT NULL ,
	[warehouse_id]       char(18)  NOT NULL 
)
go

ALTER TABLE [part_inventory]
	ADD CONSTRAINT [XPKpart_inventory] PRIMARY KEY  CLUSTERED ([part_type_id] ASC,[warehouse_id] ASC)
go


ALTER TABLE [parts]
	ADD CONSTRAINT [R_4] FOREIGN KEY ([part_type_id]) REFERENCES [part_type]([part_type_id])
		ON DELETE NO ACTION
		ON UPDATE NO ACTION
go

ALTER TABLE [parts]
	ADD CONSTRAINT [R_14] FOREIGN KEY ([part_type_id]) REFERENCES [part_type]([part_type_id])
		ON DELETE NO ACTION
		ON UPDATE NO ACTION
go


ALTER TABLE [purchase_order]
	ADD CONSTRAINT [R_7] FOREIGN KEY ([vendor_id]) REFERENCES [vendor]([vendor_id])
		ON DELETE NO ACTION
		ON UPDATE NO ACTION
go

ALTER TABLE [purchase_order]
	ADD CONSTRAINT [R_10] FOREIGN KEY ([vendor_id]) REFERENCES [vendor]([vendor_id])
		ON DELETE NO ACTION
		ON UPDATE NO ACTION
go

ALTER TABLE [purchase_order]
	ADD CONSTRAINT [R_11] FOREIGN KEY ([purord_item_id]) REFERENCES [purchase_order_items]([purord_item_id])
		ON DELETE NO ACTION
		ON UPDATE NO ACTION
go


ALTER TABLE [purchase_order_items]
	ADD CONSTRAINT [R_5] FOREIGN KEY ([purord_id]) REFERENCES [purchase_order]([purord_id])
		ON DELETE NO ACTION
		ON UPDATE NO ACTION
go

ALTER TABLE [purchase_order_items]
	ADD CONSTRAINT [R_6] FOREIGN KEY ([part_id]) REFERENCES [parts]([part_id])
		ON DELETE NO ACTION
		ON UPDATE NO ACTION
go


ALTER TABLE [staff]
	ADD CONSTRAINT [R_8] FOREIGN KEY ([department_id]) REFERENCES [department]([department_id])
		ON DELETE NO ACTION
		ON UPDATE NO ACTION
go

ALTER TABLE [staff]
	ADD CONSTRAINT [R_9] FOREIGN KEY ([pay_grade_id]) REFERENCES [pay_grade]([pay_grade_id])
		ON DELETE NO ACTION
		ON UPDATE NO ACTION
go

ALTER TABLE [staff]
	ADD CONSTRAINT [R_12] FOREIGN KEY ([department_id]) REFERENCES [department]([department_id])
		ON DELETE NO ACTION
		ON UPDATE NO ACTION
go

ALTER TABLE [staff]
	ADD CONSTRAINT [R_13] FOREIGN KEY ([pay_grade_id]) REFERENCES [pay_grade]([pay_grade_id])
		ON DELETE NO ACTION
		ON UPDATE NO ACTION
go


ALTER TABLE [part_inventory]
	ADD CONSTRAINT [R_2] FOREIGN KEY ([part_type_id]) REFERENCES [part_type]([part_type_id])
		ON DELETE NO ACTION
		ON UPDATE NO ACTION
go

ALTER TABLE [part_inventory]
	ADD CONSTRAINT [R_3] FOREIGN KEY ([warehouse_id]) REFERENCES [warehouse]([warehouse_id])
		ON DELETE NO ACTION
		ON UPDATE NO ACTION
go
